#!/bin/bash

######################################################################
# 变量定义

REPOFILE='http://mirrors.163.com/.help/CentOS6-Base-163.repo'
PKGNAME='MySQL-5.6.13-1.el6.x86_64.rpm-bundle.tar'
MYSQLOSPARTNAME='/dev/sdb1'
MYNEWPWD='xxxx'
MYCONFIG='/etc/my.cnf'
IOTHREAD=$(grep 'processor' /proc/cpuinfo | wc -l | awk '{print $0/2}')
BUFFERPOOL=$(free | grep Mem: | awk '{print $2*(4/5)/1024}' | awk -F'.' '{print $1"M"}')
MYSQLSERVERID=$(date "+%s")

######################################################################
# 安装国内yum源

yum install -y wget
cd /etc/yum.repos.d
ls | while read REPO; do mv $REPO ${REPO}.bak; done
wget ${REPOFILE}

######################################################################
# 安装perl包

yum install -y perl

######################################################################
# 安装mysql
#service mysql stop
#killall -9 mysqld
#killall -9 mysqld_safe
#mv /var/lib/mysql /var/lib/mysql.$(date "+%s")

cd /root/DBAScripts
tar -xvf ${PKGNAME}
rpm -e mysql-libs --nodeps

#mkfs.xfs ${MYSQLOSPARTNAME}
mount -o noatime,nodiratime,nobarrier ${MYSQLOSPARTNAME} /var/lib/mysql
echo "${MYSQLOSPARTNAME}               /var/lib/mysql          xfs     rw,noatime,nodiratime,nobarrier    0 2" >> /etc/fstab

rpm -ivh MySQL-*.rpm
service mysql start

######################################################################
# MySQL用户密码处理
MYPWD=$(grep ':' /root/.mysql_secret | awk -F':' '{print $4}' | sed 's/ //g' | tail -1)

mysql --connect-expired-password -uroot -p${MYPWD} <<EOF
set password for root@localhost=password("${MYNEWPWD}")
EOF

mysql -uroot -p${MYNEWPWD} <<EOF
use mysql;
delete from mysql.user where (user,host) not in(('root','localhost'));
flush privileges;
EOF

######################################################################
# MySQL参数配置

mv /usr/my.cnf /usr/my.cnf.$(date "+%s")
mv /etc/my.cnf /etc/my.cnf.$(date "+%s")

cat > ${MYCONFIG} <<EOF
[mysqld]
### GENERAL
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
max_sp_recursion_depth = 255
user = mysql
port = 3306
default_storage_engine = innodb
lower_case_table_names = 1
sql_mode = 'no_auto_create_user,no_engine_substitution'
expire_logs_days = 3
explicit_defaults_for_timestamp

### TEMP
# skip_grant_tables = 1

### PGA
sort_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
tmp_table_size = 32M
max_length_for_sort_data = 10240

### SGA
innodb_old_blocks_time = 1000
innodb_max_dirty_pages_pct = 75

### IO
innodb_io_capacity = 8000
innodb_read_io_threads = ${IOTHREAD}
innodb_write_io_threads = ${IOTHREAD}

### CBO MySQL56
#innodb_analyze_is_persistent = true
#innodb_use_sys_stats_table = true
innodb_stats_on_metadata = off

### NETWORK
net_buffer_length = 8K
max_connect_errors = 50
skip-name-resolve
max_allowed_packet = 32M
innodb_print_all_deadlocks

### INNODB
innodb_buffer_pool_size = ${BUFFERPOOL}
innodb_log_file_size = 512MB
innodb_log_files_in_group = 3
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_open_files = 4000
innodb_buffer_pool_instances = 1
open_files_limit = 4000
skip-external-locking
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = on

### Concurrency
innodb_autoinc_lock_mode = 1
innodb_commit_concurrency = 0 
innodb_concurrency_tickets = 500
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 10000

### MyISAM
key_buffer_size = 300MB
myisam_sort_buffer_size = 8M
myisam_use_mmap = on
myisam_recover_options = 'force,backup'
delay_key_write = on

### LOGGING
log_error = /var/lib/mysql/$(hostname).err
slow_query_log = /var/lib/mysql/$(hostname)-slow.log

### Security
log_bin_trust_function_creators = 1

### OTHER
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = 500
table_open_cache = 4000
thread_cache_size = 200
character_set_server = utf8

### Replication
#repalicate_wild_do_table = db1.*
#repalicate_wild_do_table = db2.*
log-bin = $(hostname).binlog
binlog_format = mixed
server-id = ${MYSQLSERVERID}
# read_only = true
skip_slave_start = true
slave_net_timeout = 60
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
sync_binlog = 1
log_slave_updates = true


[client]
socket = /var/lib/mysql/mysql.sock
port = 3306
default-character-set = utf8



[mysql]
prompt = (\u@\h) [\d]>\_
auto_rehash = false



[mysqldump]
quick
max_allowed_packet = 10485760
EOF

su - mysql -c "true > /var/lib/mysql/$(hostname).err"
service mysql restart

######################################################################
# MySQL启动日志检查

chkconfig mysql on
egrep -i '(warning|error)' /var/lib/mysql/$(hostname).err


######################################################################
# 操作系统级别的调整请手动进行

echo "OS adjust"

#echo deadline > /sys/block/sdb/queue/scheduler 
#echo 0 > /proc/sys/vm/swappiness

#cat >> /etc/rc.d/rc.local <<EOF
#echo deadline > /sys/block/sdb/queue/scheduler
#echo 0 > /proc/sys/vm/swappiness
#EOF